<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Instructor_model extends CI_Controller {

	/**
	 * Index Page for this controller.
	 *
	 * Maps to the following URL
	 * 		http://example.com/index.php/welcome
	 *	- or -  
	 * 		http://example.com/index.php/welcome/index
	 *	- or -
	 * Since this controller is set as the default controller in 
	 * config/routes.php, it's displayed at http://example.com/
	 *
	 * So any other public methods not prefixed with an underscore will
	 * map to /index.php/welcome/<method_name>
	 * @see http://codeigniter.com/user_guide/general/urls.html
	 */

	//instructor
	//view instructor's basic info
	public function setSession($number){
		$this->load->database();
		$this->load->library('session');
		
		$query = "SELECT EMPLOYEE_NO FROM instructor WHERE EMAIL_ADDRESS LIKE (select username from accounts_active where username_md5 like '".$number."')";
		$q = $this->db->query($query);
		$result = $q->result_array();
		$id = $result[0]['EMPLOYEE_NO'];
		$userdata = array('EMPLOYEE_NO' => $id);
		$this->session->set_userdata($userdata);
		

	}

	public function instructorViewInfo(){		
		$key = $this->session->userdata('EMPLOYEE_NO');
		$instructor = $this->db->query("SELECT * FROM instructor WHERE APPROVED = 'Approved' AND EMPLOYEE_NO LIKE '".$key."'");
		return $instructor->result();
	}
	
	public function instructorEditBasicInfo(){
		$key = $this->session->userdata('EMPLOYEE_NO');
		
		$mobile_no = $this->input->post('MOBILE_NO');
		$home_address = $this->input->post('HOME_ADDRESS');
		$college_address = $this->input->post('COLLEGE_ADDRESS');
		$designation_rank = $this->input->post('DESIGNATION_RANK');
		$faculty_room = $this->input->post('FACULTY_ROOM');
		$photo = $this->input->post('PHOTO1');
		
		$result = $this->db->query("
			UPDATE instructor SET Mobile_No = '".$mobile_no."',
				HOME_ADDRESS = '".$home_address."',
				COLLEGE_ADDRESS = '".$college_address."',
				DESIGNATION_RANK = '".$designation_rank."',
				FACULTY_ROOM = '".$faculty_room."',
				PHOTO = '".$key.".JPG'
			WHERE EMPLOYEE_NO = '".$key."'
		");		
	//	$a[0]=$result;
	
		return $result;
	}

	public function editInstructorPassword(){
		$key = $this->session->userdata('EMPLOYEE_NO');
		
		$new_password = md5($this->input->post('new_password1'));
		$form_old = md5($this->input->post('current_password'));

		$query = $this->db->query("SELECT * FROM instructor WHERE EMPLOYEE_NO LIKE '".$key."'");
		$old_p = $query->row();
		
		if($form_old == $old_p->PASSWORD){
			$result = $this->db->query("
				UPDATE instructor SET 
					PASSWORD = '".$new_password."'
				WHERE EMPLOYEE_NO like '".$key."'
			");
			return $result;
		}else{
			return false;
		}

	}
	
	public function instructorViewStudents($type){	
		$key = $this->session->userdata('EMPLOYEE_NO');
		
		if($type == 'reg'){
			$table = 'reg_advisee';
		}else if($type == 'sp'){
			$table = 'sp_advisee';
		}
		
		$advisee = $this->db->query("SELECT s.First_Name,s.Middle_Name, s.Last_Name,s.Email_Address from student s,".$table." r where r.EMPLOYEE_NO_instructor like '".$key."' and r.student_no_student like s.student_no");
		return $advisee->result();
	}

	public function instructorViewSchedule(){//$email_address		
		$key = $this->session->userdata('EMPLOYEE_NO');
		$instructor = $this->db->query("SELECT * FROM instructor_schedule WHERE EMPLOYEE_NO LIKE '".$key."'");
		return $instructor->result();
	}

	public function instructorViewSubject(){	
		$key = $this->session->userdata('EMPLOYEE_NO');
		$instructor = $this->db->query("SELECT DISTINCT s.Student_No, st.Last_Name FROM subject s, student st WHERE s.Subject_Type LIKE 'MAJOR' AND s.Student_No = st.Student_No AND s.Instructor_EMPLOYEE_NO LIKE '".$key."'");
		return $instructor->result();

	}

	public function viewPrerequisites($searchCourse){//	
		$key = $this->session->userdata('EMPLOYEE_NO');		
		$instructor = $this->db->query("SELECT p.Prereq_Course_Code, s.Student_No, st.First_Name, st.Middle_Name, st.Last_Name FROM prerequisite p, subject s, student st
			where p.Course_Code = '$searchCourse' AND s.Course_Code=p.Prereq_Course_Code AND s.Grade != 5.00 AND s.Student_No=st.Student_No AND s.Instructor_Employee_No LIKE '".$key."'");	
		return $instructor->result();
	}

	public function viewPreviousStudents($searchAcadYear, $searchSem){
		$key = $this->session->userdata('EMPLOYEE_NO');
		$instructor = $this->db->query("SELECT Semester, School_Year, Course_Code FROM subject WHERE Subject_Type='MAJOR' AND School_Year = '$searchAcadYear' AND Semester = '$searchSem' AND Instructor_EMPLOYEE_NO LIKE '".$key."' ");
		return $instructor->result();
	}
	
	public function viewAcadYearCourse($schoolYear, $sem, $searchAcadYearCourse){
		$key = $this->session->userdata('EMPLOYEE_NO');
		$instructor = $this->db->query("SELECT s.Student_No, st.First_Name FROM subject s, student st where s.Student_No= st.Student_No AND s.Course_Code='$searchAcadYearCourse' AND s.School_Year = '$schoolYear' AND s.Semester = '$sem' AND s.Instructor_EMPLOYEE_NO LIKE '".$key."'");
		return $instructor->result();
	}


	public function viewStudentInformation($studentNumber, $searchStudent){
		$key = $this->session->userdata('EMPLOYEE_NO');
		$instructor = $this->db->query("SELECT * from student where Student_No='$searchStudent' AND Student_No='$studentNumber' AND Approved LIKE 'Yes' ");
		return $instructor->result();
	}

	//view student information
	public function viewStudentBasicInformation($searchStudentBasic){
		$key = $this->session->userdata('EMPLOYEE_NO');
		$instructor = $this->db->query("SELECT s.Student_No, s.Email_Address, s.First_Name, s.Middle_Name, s.Last_Name, s.Photo,
		s.Home_Address, s.Home_Contact, s.College_Address, s.College_Contact, s.Mobile_No, s.Curriculum_taken, sub.Instructor_Employee_No from student s,
		subject sub where s.Student_No='$searchStudentBasic' AND sub.Semester LIKE 'second' AND sub.School_Year LIKE '2012-2013' AND sub.Instructor_Employee_No LIKE '".$key."'");
		return $instructor->result();
	}

	public function viewListOfStudents(){
		$key = $this->session->userdata('EMPLOYEE_NO');
		$instructor = $this->db->query("SELECT DISTINCT s.Student_No, s.Email_Address, s.First_Name, s.Middle_Name, s.Last_Name, s.Photo,
		s.Home_Address, s.Home_Contact, s.College_Address, s.College_Contact, s.Mobile_No, s.Curriculum_taken, sub.Instructor_Employee_No from student s,
		subject sub where sub.Semester LIKE 'second' AND sub.School_Year LIKE '2012-2013' AND s.Student_No = sub.Student_No AND sub.Instructor_Employee_No LIKE '".$key."'");
		return $instructor->result();
	}

/*****CHECKERS******/

	public function checkYearSem($searchAcadYear, $searchSem){
		$key = $this->session->userdata('EMPLOYEE_NO');
		$instructor = $this->db->query("SELECT Semester, School_Year FROM subject where Semester LIKE '$searchSem' AND School_Year='$searchAcadYear' AND Instructor_Employee_No like '".$key."' ");
		return $instructor->result();
	}

	public function checkCourse($searchCourse){
		$key = $this->session->userdata('EMPLOYEE_NO');		
		$instructor = $this->db->query("SELECT p.Prereq_Course_Code, s.Student_No, st.First_Name, st.Middle_Name, st.Last_Name FROM prerequisite p, subject s, student st
			where p.Course_Code = '$searchCourse' AND s.Course_Code=p.Prereq_Course_Code AND s.Grade != 5.00 AND s.Student_No=st.Student_No AND s.Instructor_Employee_No LIKE '".$key."'");	
		return $instructor->result();
	}

	public function checkYearCourse($schoolYear, $sem, $searchAcadYearCourse){
		$key = $this->session->userdata('EMPLOYEE_NO');
		$instructor = $this->db->query("SELECT s.Student_No, st.First_Name FROM subject s, student st where s.Student_No= st.Student_No AND s.Course_Code='$searchAcadYearCourse' AND s.School_Year = '$schoolYear' AND s.Semester = '$sem' AND s.Instructor_EMPLOYEE_NO LIKE '".$key."'");
		return $instructor->result();
	}

	public function checkStudent($studentNumber, $searchStudent){
		$key = $this->session->userdata('EMPLOYEE_NO');
		$instructor = $this->db->query("SELECT * from student where Student_No='$searchStudent' AND Student_No='$studentNumber' AND Approved LIKE 'Yes' ");
		return $instructor->result();
	}

	public function checkStudentInfo($searchStudentBasic){
		$key = $this->session->userdata('EMPLOYEE_NO');
		$instructor = $this->db->query("SELECT s.Student_No, s.Email_Address, s.First_Name, s.Middle_Name, s.Last_Name, s.Photo,
		s.Home_Address, s.Home_Contact, s.College_Address, s.College_Contact, s.Mobile_No, s.Curriculum_taken, sub.Instructor_Employee_No from student s,
		subject sub where s.Student_No='$searchStudentBasic' AND sub.Semester LIKE 'second' AND sub.School_Year LIKE '2012-2013' AND sub.Instructor_Employee_No LIKE '".$key."'");
		return $instructor->result();
	}

	public function allsubjects(){
		$instructor = $this->db->query("SELECT DISTINCT Course_Code FROM subject WHERE Course_Code LIKE 'cm%' ORDER BY Course_Code asc");
		return $instructor->result();
	}

}